# init_database.py
# 用于初始化发票管理系统数据库

import mysql.connector
from mysql.connector import Error
import sys

# 数据库配置 - 修改为你的MySQL配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'root',
    'password': 'root'  # 修改为你的MySQL密码
}

# 数据库名称
DB_NAME = 'invoice_system'


def create_database():
    """创建发票管理系统数据库"""
    try:
        # 连接MySQL服务器（不指定数据库）
        conn = mysql.connector.connect(
            host=DB_CONFIG['host'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password']
        )

        if conn.is_connected():
            cursor = conn.cursor()

            # 创建数据库
            print(f"正在创建数据库 {DB_NAME}...")
            cursor.execute(
                f"CREATE DATABASE IF NOT EXISTS {DB_NAME} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci")
            print(f"数据库 {DB_NAME} 创建成功！")

            # 切换到新创建的数据库
            cursor.execute(f"USE {DB_NAME}")

            # 创建发票表
            print("正在创建发票表...")
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS invoices (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    invoice_number VARCHAR(50) UNIQUE COMMENT '发票号码',
                    invoice_date DATE COMMENT '开票日期',
                    seller_name VARCHAR(100) COMMENT '销售方名称',
                    purchaser_name VARCHAR(100) COMMENT '购买方名称',
                    item_name VARCHAR(100) COMMENT '项目名称',
                    amount_without_tax DECIMAL(10, 2) COMMENT '不含税金额',
                    tax DECIMAL(10, 2) COMMENT '税额',
                    amount_with_tax DECIMAL(10, 2) COMMENT '含税金额',
                    image_path VARCHAR(255) COMMENT '发票图片路径',
                    upload_date DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '上传日期',
                    INDEX idx_invoice_date (invoice_date),
                    INDEX idx_seller_name (seller_name),
                    INDEX idx_purchaser_name (purchaser_name),
                    INDEX idx_item_name (item_name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发票信息表'
            ''')

            print("发票表创建成功！")
            conn.commit()

            return True

    except Error as e:
        print(f"数据库初始化错误: {e}")
        return False

    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()
            print("数据库连接已关闭。")


def test_connection():
    """测试数据库连接"""
    try:
        # 连接到已创建的数据库
        conn = mysql.connector.connect(
            host=DB_CONFIG['host'],
            user=DB_CONFIG['user'],
            password=DB_CONFIG['password'],
            database=DB_NAME
        )

        if conn.is_connected():
            db_info = conn.get_server_info()
            print(f"已连接到MySQL服务器，版本: {db_info}")

            cursor = conn.cursor()
            cursor.execute("SELECT DATABASE();")
            db_name = cursor.fetchone()[0]
            print(f"当前数据库: {db_name}")

            # 检查发票表是否存在
            cursor.execute("SHOW TABLES;")
            tables = cursor.fetchall()
            print(f"数据库中的表: {[table[0] for table in tables]}")

            return True

    except Error as e:
        print(f"测试连接时出错: {e}")
        return False

    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()
            print("测试连接已关闭。")


if __name__ == "__main__":
    print("=== 发票管理系统数据库初始化工具 ===")
    print("注意：请确保已安装MySQL并已启动MySQL服务。")
    print(f"当前数据库配置: 主机={DB_CONFIG['host']}, 用户={DB_CONFIG['user']}")

    # 创建数据库
    if create_database():
        print("\n数据库初始化成功！")

        # 测试连接
        print("\n正在测试数据库连接...")
        if test_connection():
            print("\n数据库连接测试成功！系统已准备就绪。")
        else:
            print("\n数据库连接测试失败！请检查配置并重试。")
    else:
        print("\n数据库初始化失败！请检查MySQL配置和权限设置。")